import sys
import os
import pandas as pd
from PyQt6.QtWidgets import QApplication, QWidget, QVBoxLayout, QHBoxLayout, QPushButton, QFileDialog, QLineEdit, QLabel, QComboBox, QTextEdit, QMessageBox
from PyQt6.QtCore import Qt, QSettings

class ExcelMerger(QWidget):
    def __init__(self):
        super().__init__()
        self.settings = QSettings("个人制作", "Excel工作表合并1.0")
        self.initUI()
        # 尝试加载上次使用的路径和文件名
        self.loadLastUsed()

    def initUI(self):
        # 布局设置
        mainLayout = QVBoxLayout()

        # 文件夹路径选择
        folderLayout = QHBoxLayout()
        self.folderLabel = QLabel('文件夹路径:')
        self.folderLineEdit = QLineEdit()
        self.browseFolderButton = QPushButton('浏览')
        self.browseFolderButton.clicked.connect(self.browseFolder)
        folderLayout.addWidget(self.folderLabel)
        folderLayout.addWidget(self.folderLineEdit)
        folderLayout.addWidget(self.browseFolderButton)

        # 保存文件名选择
        saveLayout = QHBoxLayout()
        self.saveLabel = QLabel('保存文件名:')
        self.saveLineEdit = QLineEdit()
        self.browseSaveButton = QPushButton('浏览')
        self.browseSaveButton.clicked.connect(self.browseSaveFile)
        saveLayout.addWidget(self.saveLabel)
        saveLayout.addWidget(self.saveLineEdit)
        saveLayout.addWidget(self.browseSaveButton)

        # 工作表选择
        sheetLayout = QHBoxLayout()
        self.sheetLabel = QLabel('选择工作表:')
        self.sheetComboBox = QComboBox()
        sheetLayout.addWidget(self.sheetLabel)
        sheetLayout.addWidget(self.sheetComboBox)

        # 保存按钮
        self.saveButton = QPushButton('保存文件')
        self.saveButton.clicked.connect(self.mergeFiles)

        # 日志显示
        self.logTextEdit = QTextEdit()
        self.logTextEdit.setReadOnly(True)

        # 添加到主布局
        mainLayout.addLayout(folderLayout)
        mainLayout.addLayout(saveLayout)
        mainLayout.addLayout(sheetLayout)
        mainLayout.addWidget(self.saveButton)
        mainLayout.addWidget(self.logTextEdit)

        # 设置窗口属性
        self.setLayout(mainLayout)
        self.setWindowTitle('Excel工作表合并1.0')
        self.setGeometry(300, 300, 600, 400)

    def browseFolder(self):
        folderName = QFileDialog.getExistingDirectory(self, '选择文件夹')
        if folderName:
            self.folderLineEdit.setText(folderName)
            self.loadSheets(folderName)  # 加载工作表
            self.saveSettings()  # 保存设置

    def browseSaveFile(self):
        fileName, _ = QFileDialog.getSaveFileName(self, '保存文件', '', 'Excel 文件 (*.xlsx *.xls)')
        if fileName:
            self.saveLineEdit.setText(fileName)
            self.saveSettings()  # 保存设置

    def loadSheets(self, folderName):
        # 清空下拉列表
        self.sheetComboBox.clear()
        try:
            # 尝试读取第一个Excel文件来获取工作表名称
            for file in os.listdir(folderName):
                if file.endswith('.xls') or file.endswith('.xlsx'):
                    filePath = os.path.join(folderName, file)
                    try:
                        with pd.ExcelFile(filePath) as xls:
                            sheets = xls.sheet_names
                            self.sheetComboBox.addItems(sheets)
                            break  # 只需加载一次
                    except Exception as e:
                        self.log(f'无法打开文件 {file}: {e}')
        except Exception as e:
            self.log(f'读取文件夹失败: {e}')

    def mergeFiles(self):
        folderPath = self.folderLineEdit.text()
        savePath = self.saveLineEdit.text()
        selectedSheet = self.sheetComboBox.currentText()

        if not os.path.exists(folderPath):
            self.log('指定的文件夹不存在')
            return

        if not savePath:
            self.log('未指定保存文件名')
            return

        if not selectedSheet:
            self.log('请选择一个工作表')
            return

        # 检查文件是否被占用
        if os.path.exists(savePath):
            try:
                os.rename(savePath, savePath)
            except OSError:
                QMessageBox.critical(self, '错误', '目标文件已被占用，请关闭文件后重试。')
                return

        all_data = []
        for file in os.listdir(folderPath):
            if file.endswith('.xls') or file.endswith('.xlsx'):
                filePath = os.path.join(folderPath, file)
                try:
                    data = pd.read_excel(filePath, sheet_name=selectedSheet)
                    all_data.append(data.dropna(how='all'))  # 删除空行
                    self.log(f'成功读取文件: {file}')
                except Exception as e:
                    self.log(f'读取文件 {file} 失败: {e}')

        if all_data:
            result = pd.concat(all_data, ignore_index=True)
            try:
                result.to_excel(savePath, index=False)
                self.log(f'文件已保存至: {savePath}')
            except Exception as e:
                self.log(f'保存文件失败: {e}')
        else:
            self.log('没有文件可以合并')

    def log(self, message):
        self.logTextEdit.append(message)

    def loadLastUsed(self):
        # 从 QSettings 中加载上次使用的路径和文件名
        lastFolderPath = self.settings.value('lastFolderPath', '')
        lastSavePath = self.settings.value('lastSavePath', '')
        if lastFolderPath and os.path.exists(lastFolderPath):
            self.folderLineEdit.setText(lastFolderPath)
            self.loadSheets(lastFolderPath)
        if lastSavePath:
            self.saveLineEdit.setText(lastSavePath)

    def saveSettings(self):
        # 保存当前的路径和文件名到 QSettings
        self.settings.setValue('lastFolderPath', self.folderLineEdit.text())
        self.settings.setValue('lastSavePath', self.saveLineEdit.text())

if __name__ == '__main__':
    app = QApplication(sys.argv)
    ex = ExcelMerger()
    ex.show()
    sys.exit(app.exec())